package dao;

import domain.Car;
import util.C3P0Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CarDao {
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    //用户登录
    public boolean user_login(String username, String password) throws SQLException {
        connection = C3P0Util.getConnection();

        String sql = "select count (*) from j_user where username =? and password =?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);
        resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            int result = resultSet.getInt(1);
            if (result > 0)
                return true;
            else
                return false;
        }
        C3P0Util.closeAll(resultSet, preparedStatement, connection);
        return false;
    }

    //查询
    public List<Car> query() throws SQLException {
        connection = C3P0Util.getConnection();
        String sql = "select * from j_car";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();

        List<Car> carList = new ArrayList<>();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String carnumber = resultSet.getString("carnumber");
            String type = resultSet.getString("type");
            int isnew = resultSet.getInt("isnew");
            int isrepair = resultSet.getInt("isrepair");
            carList.add(new Car(id, carnumber, type, isnew, isrepair));
        }
        C3P0Util.closeAll(resultSet, preparedStatement, connection);
        return carList;
    }

    //插入
    public int insert_car(Car car) throws SQLException {
        connection = C3P0Util.getConnection();
        String sql = "insert into J_car(ID,Carnumber,type,isnew,isrepair) " +
                " values (seq_j_car.nextval,?,?,?,0)";

        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, car.getCarnumber());
        preparedStatement.setString(2, car.getType());
        preparedStatement.setInt(3, car.getIsnew());
        int result = preparedStatement.executeUpdate();
        C3P0Util.closeAll(resultSet, preparedStatement, connection);
        return result;
    }

    //删除
    public int delete_car(Car car) throws SQLException {
        connection = C3P0Util.getConnection();
        String sql = "delete from j_car where carnumber =?";

        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, car.getCarnumber());
        int result = preparedStatement.executeUpdate();
        C3P0Util.closeAll(resultSet, preparedStatement, connection);
        return result;

    }

    public int update_car(Car car) throws SQLException {
        connection = C3P0Util.getConnection();
        String sql = "update J_car set " +
                "isrepair =? where carnumber=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, car.getIsrepair());
        preparedStatement.setInt(2, car.getIsrepair());
        int result = preparedStatement.executeUpdate();
        C3P0Util.closeAll(resultSet, preparedStatement, connection);
        return result;


    }
}
